package com.xs.gis.cover.coverImpl;

import com.xs.gis.cover.FileCover;
import com.xs.gis.domain.geojson.*;
import com.xs.gis.util.CRSUtils;
import com.xs.gis.util.JacksonUtil;
import com.xs.gis.domain.ExcelPoint;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.geotools.geojson.geom.GeometryJSON;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.json.simple.JSONObject;
import org.locationtech.jts.geom.*;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static java.sql.Types.BOOLEAN;
import static java.sql.Types.NUMERIC;
import static org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType.FORMULA;
import static org.apache.xmlbeans.impl.piccolo.xml.Piccolo.STRING;

public class ExcelCover implements FileCover {
    /**
     * 2003- 版本的excel
     */
    private final static String excel2003L = ".xls";

    /**
     * 2007版本
     */
    private final static String excel2007U = ".xlsx";


    private String text;

    @Override
    public String featureToFile(GeoJsonFeature jsonfeature, String filePath) throws IOException {
        try {
            List<GeoFeature> features = jsonfeature.getFeatures();
            List<ExcelPoint> excelPointAll = new ArrayList();
            for (GeoFeature feature : features) {
                Map<String, Object> properties = feature.getProperties();
                Geometry geometry = feature.formatGeometry();
                if (geometry instanceof LineString) {
                    LineString lineString = (LineString) geometry;
                    List<ExcelPoint> excelPoints = writePoint(lineString, 1,properties);
                    excelPointAll.addAll(excelPoints);
                }
                if (geometry instanceof Polygon) {
                    Polygon polygon = (Polygon) geometry;
                    List<ExcelPoint> excelPoints = writePoint(polygon, 1,properties);
                    excelPointAll.addAll(excelPoints);
                }
                //多面
                if (geometry instanceof MultiPolygon) {
                    MultiPolygon multiPolygon = (MultiPolygon) geometry;
                    int numGeometries = multiPolygon.getNumGeometries();

                    for (int i = 0; i < numGeometries; i++) {
                        Polygon geometryN = (Polygon) multiPolygon.getGeometryN(i);
                        List<ExcelPoint> excelPoints = writePoint(geometryN, i + 1,properties);
                        excelPointAll.addAll(excelPoints);
                    }
                }
            }
            exportExcel(filePath, excelPointAll);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return filePath;
    }

    @Override
    public GeoJsonFeature fileToFeature(String filePath) {
        try {
            FileInputStream inputStream = new FileInputStream(new File(filePath));
            //创建Excel工作薄
            Workbook work = getWorkbook(inputStream, filePath);

            if (null == work) {
                throw new Exception("创建Excel工作薄为空！");
            }
            //遍历Excel中所有的sheet
            return getFeatures(work);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 数据导出
     *
     * @throws Exception
     */
    private static void exportExcel(String filePath, List<ExcelPoint> excelPoints) throws Exception {
        try {
            // 创建表头
            // 创建工作薄
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            // 创建表头行
            Row rowHeader = sheet.createRow(0);
            List<String> headerList = new ArrayList<>();
            headerList.add("界址点号");
            headerList.add("纵坐标(X)");
            headerList.add("横坐标(Y)");
            headerList.add("地块圈号");
            Map<String, Object> properties = excelPoints.get(0).getProperties();
            if (Objects.nonNull(properties)) {
                properties.entrySet().forEach(x -> {
                    String key = x.getKey();
                    headerList.add(key);
                });
            }

            for (int i = 0; i < headerList.size(); i++) {
                Cell cell = rowHeader.createCell(i, CellType.STRING);
                String headerName = headerList.get(i);
                cell.setCellValue(headerName);
            }
            // 填充数据
            if (!CollectionUtils.isEmpty(excelPoints)) {
                for (int o = 0; o < excelPoints.size(); o++) {
                    Row rowData = sheet.createRow(o + 1);
                    ExcelPoint excelPoint = excelPoints.get(o);
                    Field[] fields = excelPoint.getClass().getDeclaredFields();
                    for (int i = 0; i < fields.length; i++) {
                        Field f = excelPoint.getClass().getDeclaredField(fields[i].getName());
                        f.setAccessible(true);
                        String value = f.get(excelPoint).toString();
                        if("properties". equals(fields[i].getName())){
                            Map<String, Object> objectMap = excelPoint.getProperties();
                            for (int j = 4; j < headerList.size(); j++) {
                                Cell cell = rowData.createCell(j);
                                value = new String();
                                Object object = objectMap.get(headerList.get(j));
                                if(object != null){
                                    value = objectMap.get(headerList.get(j)).toString();
                                }
                                cell.setCellValue(value);
                            }
                        }else{
                            Cell cell = rowData.createCell(i);
                            if("pointIndex". equals(fields[i].getName())){
                                value = "J" + value;
                            }
                            cell.setCellValue(value);
                        }

                    }
                }
            }
            File file = new File(filePath);
            //将文件保存到指定的位置
            try {
                workbook.write(new FileOutputStream(file));
                System.out.println("写入成功");
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 写入点
     * @param geometry
     * @param index
     * @return
     * @throws IOException
     */
    private List<ExcelPoint> writePoint(Geometry geometry, Integer index,Map<String, Object> properties) throws IOException {

        List<ExcelPoint> excelPoints = new ArrayList<>();
        Coordinate[] coordinates = geometry.getCoordinates();
        for (int i = 0; i < coordinates.length; i++) {
            Coordinate coordinate = coordinates[i];
            int pointIndex = i + 1;
            if (pointIndex == coordinates.length) {
                pointIndex = 1;
            }
            ExcelPoint point = ExcelPoint.builder().pointIndex(pointIndex).polygonIndex(index)
                    .x(new BigDecimal(String.valueOf(coordinate.getX())))
                    .y(new BigDecimal(String.valueOf(coordinate.getY())))
                    .properties(properties).build();
            excelPoints.add(point);
        }
        return excelPoints;

    }



    /**
     * 处理每页 每行数据
     *
     * @param work
     * @return
     */
    private GeoJsonFeature getFeatures(Workbook work) {
        //页数
        Sheet sheet = null;
        //行数
        Row row = null;
        //列数
        Cell cell = null;
        String context = text;
        StringBuffer fileContent = new StringBuffer();
        List<ExcelPoint> excelPoints = new ArrayList<>();
        List<GeoFeature> features = new ArrayList<>();
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    // 遍历当前行的所有列
                    cell = row.getCell(y);
                    fileContent.append(cell).append(",");
                }
                fileContent.deleteCharAt(fileContent.lastIndexOf(",")).append("\n");
            }
        }
        String tapeNumber = new String();
        context = fileContent.toString();
        String[] split = context.split("\n");
        List<String> list = Arrays.asList(split);
        Map<Integer, List<ExcelPoint>> collect = new HashMap<>();
        Map<String,Object> properties = new HashMap<>();
        int index = 1;
        for (int i = 1; i < list.size(); i++) {
            String fileNames = list.get(0);
            List<String> fileName = Arrays.asList(fileNames.split(","));
            String readLine = list.get(i);
            List<String> messages = Arrays.asList(readLine.split(","));

            for (int j = 4; j < messages.size(); j++) {
                properties.put(fileName.get(j),messages.get(j));
            }
            String y = messages.get(1);
            String x = messages.get(2);
            tapeNumber = new BigDecimal(x).toString().substring(0, 2);
            ExcelPoint excelPoint = ExcelPoint.builder()
                    .pointIndex(Integer.parseInt(messages.get(0).replace("J", "")))
                    .polygonIndex(Integer.parseInt(messages.get(3)))
                    .x(new BigDecimal(x))
                    .y(new BigDecimal(y))
                    .properties(properties)
                    .build();
            if(CollectionUtils.isEmpty(excelPoints)){
                excelPoints.add(excelPoint);
            }else {
                ExcelPoint point = excelPoints.get(0);
                if(excelPoint.getPointIndex() == 1 && excelPoint.getPolygonIndex() == 1
                        && !point.getX().equals(excelPoint.getX()) && !point.getY().equals(excelPoint.getY())){
                    collect.put(index,excelPoints);
                    excelPoints.clear();
                    excelPoints.add(excelPoint);
                    index ++ ;
                }else {
                    excelPoints.add(excelPoint);
                    if(i == list.size() -1){
                        collect.put(index,excelPoints);
                    }
                }
            }
        }
        for (Map.Entry<Integer, List<ExcelPoint>> listEntry : collect.entrySet()) {
            List<ExcelPoint> value = listEntry.getValue();
            GeoFeature geoFeature = buildFeature(value);
            features.add(geoFeature);
        }
        GeoJsonFeature geoJsonFeature = new GeoJsonFeature();
        String epsgCode = CRSUtils.getCRSByUTM(CRSUtils.CGCS2000, tapeNumber);
        GeoCrs geoCrs = new GeoCrs();
        GeoCrsProperties geoCrsProperties = new GeoCrsProperties();
        geoCrsProperties.setName(epsgCode);
        geoCrs.setType("name");
        geoCrs.setProperties(geoCrsProperties);
        geoJsonFeature.setCrs(geoCrs);
        geoJsonFeature.setType("FeatureCollection");
        geoJsonFeature.setFeatures(features);

        return geoJsonFeature;
    }


    private GeoFeature buildFeature(List<ExcelPoint> excelPoints) {
        //构建属性
        JSONObject localProperty = new JSONObject();
        GeoFeature geoFeature = new GeoFeature();
        Geometry geometry = parserTxtPoint(excelPoints);
        Map<String, Object> properties = excelPoints.get(0).getProperties();
        localProperty.putAll(properties);
        geoFeature.setProperties(localProperty);
        GeometryJSON g = new GeometryJSON(15);
        String geoJSON = g.toString(geometry);
        geoFeature.setGeometry(JacksonUtil.toObject(geoJSON, BaseGeometry.class));
        geoFeature.setType("Feature");
        return geoFeature;
    }

    private Geometry parserTxtPoint(List<ExcelPoint> excelPoints) {

        GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory();

        Map<Integer, List<ExcelPoint>> pointMap = excelPoints.stream().collect(Collectors.groupingBy(ExcelPoint::getPolygonIndex));
        List<Polygon> polygons = new ArrayList<>();
        for (Integer index : pointMap.keySet()) {
            List<ExcelPoint> subTxtPoints = pointMap.get(index);
            List<Coordinate> pointList = subTxtPoints.stream().map(x ->
                    new Coordinate(x.getX().doubleValue(), x.getY().doubleValue())
            ).collect(Collectors.toList());
            Polygon polygon = geometryFactory.createPolygon(pointList.toArray(new Coordinate[pointList.size()]));
            polygons.add(polygon);
        }
        if (polygons.size() > 1) {
            return geometryFactory.createMultiPolygon(polygons.toArray(new Polygon[0]));
        } else {
            return polygons.get(0);
        }


    }

    /**
     * 根据文件后缀，自适应上传文件的版本
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            //2003-
            wb = new HSSFWorkbook(inStr);
        } else if (excel2007U.equals(fileType)) {
            //2007+
            wb = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    value = format.format(date);
                } else {// 纯数字
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if (null != value && !"".equals(value.trim())) {
                        String[] item = value.split("[.]");
                        if (1 < item.length && "0".equals(item[1])) {
                            value = item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case STRING:
                value = cell.getStringCellValue();
                break;
            // 公式类型
            case FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                // 如果获取的数据值为非法值,则转换为获取字符串
                if (value.equals("NaN")) {
                    value = cell.getStringCellValue();
                }
                break;
            // 布尔类型
            case BOOLEAN:
                value = " " + cell.getBooleanCellValue();
                break;
            default:
                cell.setCellType(CellType.STRING);
                value = cell.getStringCellValue();
        }
        if ("null".endsWith(value.trim())) {
            value = "";
        }
        return value;
    }
}